<?php

class ConsultaController extends RController
{
    
        public function filters() {    
            return array('rights',);             
        }  
        
        public function allowedActions(){
            return 'index,publico';             
       } 
        
	public function actionIndex()
	{
		$this->render('index');
	}

        
         public function actionDocumentoInfo()
        {
            if (Yii::app()->request->isAjaxRequest) {
                $id = $_POST['Did'];  
                
                $dataProviderHistoria = $this->BusquedaFlujos($id);
                $datos = $dataProviderHistoria->getData();
                
                $texto= "<p>";
                $texto.= CHtml::link('Consultar Más Información utilizando la Red Interna',"http://192.168.100.91/gestor/view.php?fDocumentId=".$id,array('target'=>'_blank'));
                $texto.= "<br/>";
                $texto.= CHtml::link('Consultar Más Información utilizando Internet',"http://mail.manta.gob.ec:8223/gestor/view.php?fDocumentId=".$id,array('target'=>'_blank'));
                $texto.= "<br/>Fecha:día/mes/año</p>";
                
                $texto.="<table>";
                
                $texto.="<tbody>
                        <thead>
                        <tr><th>Fecha</th><th>Hora</th><th>Usuario</th><th>Descripcion</th></tr>
                        </thead>                    
                ";
                
                foreach ($datos as $provider) 
                    $texto.="<tr><td>".Yii::app()->dateFormatter->formatDateTime(CDateTimeParser::parse($provider['created'], 'yyyy-MM-dd hh:mm:ss'),'short',null)."</td><td>{$provider['hora']}</td><td>{$provider['username']}</td><td>{$provider['comment']}</td></tr>";
                
                    
                $texto.="<tbody>";
                $texto.="</table>";
                echo $texto;               
                
            }
        }
        
	public function actionPrivado()
	{
            
       if(isset($_POST['codigo']))           
             Yii::app()->user->setState('patron',$_POST['codigo']);
       
         //$codigoBusqueda = $_POST['codigo'];
         $codigoBusqueda =Yii::app()->user->getState('patron');    
         if(isset($codigoBusqueda))
         {
                //=Yii::app()->user->getState('patron');                               
            //$codigoBusqueda= "gustavo";                               
                
                //Query para documentos general
                $dataProviderPatron=$this->BusquedaPorPatron($codigoBusqueda);                                              
                     
		$this->render('privadoresultado',array('dataProviderPatron'=>$dataProviderPatron,'codigo'=>$codigoBusqueda));                                  
           }
           else                
		$this->render('privado');
	}
        
        public function actionTags()
        {
            
        }
        
        private function BusquedaPorPatron($codigoBusqueda)
        {
            $count=Yii::app()->db->createCommand("SELECT count(d.id) FROM documents d 
			    inner join document_fields_link dfl on d.metadata_version_id = dfl.metadata_version_id  
			    inner join document_fields df on dfl.document_field_id = df.id
			    inner join document_metadata_version dmv on dmv.id = d.metadata_version_id
			    inner join workflows w on dmv.workflow_id = w.id                
                            where w.name like '%$codigoBusqueda%' or dfl.value like '%$codigoBusqueda%'")->queryScalar();
                            
               $sql = "SELECT d.id as documento_id,d.created,d.creator_id,w.name as tramite,dfl.value as coincidencia FROM documents d 
			    inner join document_fields_link dfl on d.metadata_version_id = dfl.metadata_version_id  
			    inner join document_fields df on dfl.document_field_id = df.id
			    inner join document_metadata_version dmv on dmv.id = d.metadata_version_id
			    inner join workflows w on dmv.workflow_id = w.id                
                            where w.name like '%$codigoBusqueda%' or dfl.value like '%$codigoBusqueda%'";             

               $dataProviderPatron=new CSqlDataProvider($sql, array(
                    'totalItemCount'=>$count,
                    'keyField'=>'documento_id',
                    /*'sort'=>array(
                        'attributes'=>array(
                             'created','creator_id', 'name', 'value',
                        ),
                    ),*/
                    'pagination'=>array(
                        'pageSize'=>10,
                    ),
                ));
                
                return $dataProviderPatron;

        
        }
                
        
        private function BusquedaGeneralDocumento($codigoBusqueda)
        {
            $count=Yii::app()->db->createCommand('SELECT count(d.created) FROM documents d 
			    inner join document_fields_link dfl on d.metadata_version_id = dfl.metadata_version_id  
			    inner join document_fields df on dfl.document_field_id = df.id
			    inner join document_metadata_version dmv on dmv.id = d.metadata_version_id
			    inner join workflows w on dmv.workflow_id = w.id
                                    where d.id = '.$codigoBusqueda)->queryScalar();
                
                $sql='SELECT d.created,d.creator_id,df.name,dfl.value,dfl.metadata_version_id,w.name as tramite,dfl.document_field_id,u.username FROM documents d 
                            inner join users as u on d.creator_id = u.id
			    inner join document_fields_link dfl on d.metadata_version_id = dfl.metadata_version_id  
			    inner join document_fields df on dfl.document_field_id = df.id
			    inner join document_metadata_version dmv on dmv.id = d.metadata_version_id
			    inner join workflows w on dmv.workflow_id = w.id
                                    where d.id = '.$codigoBusqueda;

                $dataProviderGeneral=new CSqlDataProvider($sql, array(
                    'totalItemCount'=>$count,
                    'keyField'=>'creator_id',
                    /*'sort'=>array(
                        'attributes'=>array(
                             'created','creator_id', 'name', 'value',
                        ),
                    ),*/
                    'pagination'=>array(
                        'pageSize'=>10,
                    ),
                ));
                
                return $dataProviderGeneral;
            
        }
        
        private function BusquedaFlujos($codigoBusqueda)
        {
             $count=Yii::app()->db->createCommand("SELECT count(d.created) FROM documents d 
			    inner join document_transactions dt on d.id = dt.document_id    
			    where dt.transaction_namespace='ktcore.transactions.workflow_state_transition' and d.id = $codigoBusqueda ")->queryScalar();                  
                
                     
                     $sql="SELECT d.created,d.creator_id,dt.user_id,dt.datetime,TIME(dt.datetime) as hora,dt.comment,u.username FROM documents d                             
			    inner join document_transactions dt on d.id = dt.document_id    
                            inner join users as u on dt.user_id = u.id
			    where dt.transaction_namespace='ktcore.transactions.workflow_state_transition' and d.id = $codigoBusqueda ";
               
                     
                     $dataProviderHistoria=new CSqlDataProvider($sql, array(
                    'totalItemCount'=>$count,
                    'keyField'=>'creator_id',
                    /*'sort'=>array(
                        'attributes'=>array(
                             'created','creator_id', 'name', 'value',
                        ),
                    ),*/
                    'pagination'=>array(
                        'pageSize'=>10,
                    ),
                ));
                     
                     return $dataProviderHistoria;
                     
        }

        public function actionPublico()
	{
          
            if(isset($_POST['codigo']) && is_numeric($_POST['codigo']))                     
                Yii::app()->user->setState('publico',$_POST['codigo']);
         
            $codigoBusqueda =Yii::app()->user->getState('publico');    
            if(isset($codigoBusqueda))
            {                                                                
                //Query para documentos general
                $dataProviderGeneral=$this->BusquedaGeneralDocumento($codigoBusqueda);
                
                //Query para obtener historia del documento                
                $dataProviderHistoria= $this->BusquedaFlujos($codigoBusqueda);
                     
		$this->render('publicoresultado',array('dataProviderGeneral'=>$dataProviderGeneral, 'dataProviderHistoria'=>$dataProviderHistoria,'codigo'=>$codigoBusqueda));            
                      
            }
            else
                $this->render('publico');
	}
        
 
	// Uncomment the following methods and override them if needed
	/*
	public function filters()
	{
		// return the filter configuration for this controller, e.g.:
		return array(
			'inlineFilterName',
			array(
				'class'=>'path.to.FilterClass',
				'propertyName'=>'propertyValue',
			),
		);
	}
*/
        
	

      /*
        public function filters()
	{
		return array(
			'accessControl', // perform access control for CRUD operations
		);
	}
        */
	
}